package dao;

import java.io.FileOutputStream;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
import java.util.Scanner;

import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.CellStyle;
import org.apache.poi.ss.util.CellRangeAddress;

/**
 * PersonDao的具体实现类
 * @author lamp
 *
 */
public class StudentDaoJDB implements StudentDao{

    /**
     * 实现添加方法
     */
	public boolean writeStudent(Student student) {
        Connection conn = null;
        PreparedStatement ps = null;
        String sql = "insert into person(stunum,name,classes,gender,javaScore,mathScore,englishScore)values(?,?,?,?,?,?,?,?)";
        int result = -1;
        try{
        	conn = JDBCUtil.getConnection();
            ps = conn.prepareStatement(sql);
            ps.setString(1, student.getStunum());
            ps.setString(2, student.getName());
            ps.setInt(3, student.getClasses());
            ps.setString(4, student.getGender());
            ps.setDouble(5, student.getScoreList().get(0).getScore());
            ps.setDouble(6, student.getScoreList().get(1).getScore());
            ps.setDouble(7, student.getScoreList().get(2).getScore());
            result = ps.executeUpdate();
            System.out.println("添加成功！");
        }catch(SQLException e){
            e.printStackTrace();
            try {
				throw new SQLException("添加数据失败");
			} catch (SQLException e1) {
				// TODO Auto-generated catch block
				e1.printStackTrace();
			}
        }finally{
            JDBCUtil.close(null, ps, conn);
        }
		return result>0?true:false;
    }

    /**
     * 更新方法
     * @return 
     */
    @Override
    public boolean update(Student s) {
        Connection conn = null;
        PreparedStatement ps = null;
        String sql = "update students set javaScore=?,mathScore=?,englishScore=? where stunum=?";
        try{
            conn = JDBCUtil.getConnection();
            ps = conn.prepareStatement(sql);
            
	        ps.setDouble(1,s.getScoreList().get(0).getScore());       
	        ps.setDouble(2,s.getScoreList().get(1).getScore());
	        ps.setDouble(3,s.getScoreList().get(2).getScore());
	        ps.setString(4, s.getStunum());
	        
	        ps.executeUpdate();
          System.out.println("修改成功！");
            ps.executeUpdate();
            System.out.println("修改成功！");
        }catch(SQLException e){
            e.printStackTrace();
            try {
				throw new SQLException("更新数据失败");
			} catch (SQLException e1) {
				// TODO Auto-generated catch block
				e1.printStackTrace();
			}
        }finally{
            JDBCUtil.close(null, ps, conn);
        }
		return false;        
    }

    /**
     * 删除方法
     * @return 
     */
    @Override
    public boolean delete(String stunum) {
        Connection conn = null;
        PreparedStatement ps = null;
        String sql = "delete from person where stunum=?";
        try{
            conn = JDBCUtil.getConnection();
            ps = conn.prepareStatement(sql);
            ps.setString(1,stunum);   //stunum为传入的需要删除的数据
            ps.executeUpdate();
        }catch(SQLException e){
            e.printStackTrace();
            try {
				throw new SQLException(" 删除数据失败");
			} catch (SQLException e1) {
				// TODO Auto-generated catch block
				e1.printStackTrace();
			}
        }finally{
            JDBCUtil.close(null, ps, conn);
        }
		return false;        
    }
	/**
     * 按 id 查询学生成绩
     */
	@Override
	public Student findStudentById(int id) {
		Connection conn = null;
        PreparedStatement ps = null;
        ResultSet rs = null;
        Student s = null;
        List<Student> students = new ArrayList<Student>();
        System.out.println("学号" + "\t" + "姓名"+ "\t" + "班级"+ "\t" + "性别"+ "\t" + "Java成绩"+ "\t" + "数学成绩"+ "\t" + "英语成绩");  
        System.out.println("-----------------");  
        String sql ="select * from students where id like '%"+id+"%'";//班级
        try{
            conn = JDBCUtil.getConnection();
            ps = conn.prepareStatement(sql);
            rs = ps.executeQuery();
            while(rs.next()){
                s = new Student();
                s.setStunum(rs.getString("stunum"));
                s.setName(rs.getString("name"));
                s.setClasses(rs.getInt("classes"));
                s.setGender(rs.getString("gender"));
                s.getScoreList().get(0).setScore(rs.getDouble("javaScore"));
                s.getScoreList().get(1).setScore(rs.getDouble("mathScore"));
                s.getScoreList().get(2).setScore(rs.getDouble("englishScore"));
                students.add(s);
                
//                System.out.println(stunum+"\t"+name+"\t"+classes+"\t"+gender+"\t"+javaScore+
//            			"\t"+mathScore+"\t"+englishScore);//输出查询结果
            }
            System.out.println(students);
        }catch(SQLException e){
            e.printStackTrace();
            try {
				throw new SQLException("查询所有数据失败");
			} catch (SQLException e1) {
				// TODO Auto-generated catch block
				e1.printStackTrace();
			}
        }finally{
            JDBCUtil.close(rs, ps, conn);
        }
        return (Student) students;
	}
	/**
     * 按 姓名 查询学生成绩
     */
	@Override
	public List<Student> findStudentByName(String name) {
		Connection conn = null;
        PreparedStatement ps = null;
        ResultSet rs = null;
        Student s = null;
        List<Student> students = new ArrayList<Student>();
        System.out.println("学号" + "\t" + "姓名"+ "\t" + "班级"+ "\t" + "性别"+ "\t" + "Java成绩"+ "\t" + "数学成绩"+ "\t" + "英语成绩");  
        System.out.println("-----------------");  
        String sql ="select * from students where name like '%"+name+"%'";//班级
        try{
            conn = JDBCUtil.getConnection();
            ps = conn.prepareStatement(sql);
            rs = ps.executeQuery();
            while(rs.next()){
                s = new Student();
                s.setStunum(rs.getString("stunum"));
                s.setName(rs.getString("name"));
                s.setClasses(rs.getInt("classes"));
                s.setGender(rs.getString("gender"));
                s.getScoreList().get(0).setScore(rs.getDouble("javaScore"));
                s.getScoreList().get(1).setScore(rs.getDouble("mathScore"));
                s.getScoreList().get(2).setScore(rs.getDouble("englishScore"));
                students.add(s);
                
//                System.out.println(stunum+"\t"+name+"\t"+classes+"\t"+gender+"\t"+javaScore+
//            			"\t"+mathScore+"\t"+englishScore);//输出查询结果
            }
            System.out.println(students);
        }catch(SQLException e){
            e.printStackTrace();
            try {
				throw new SQLException("查询所有数据失败");
			} catch (SQLException e1) {
				// TODO Auto-generated catch block
				e1.printStackTrace();
			}
        }finally{
            JDBCUtil.close(rs, ps, conn);
        }
        return students;
	}
	/**
     * 按 学号 查询学生成绩
     */
	@Override
	public List<Student> findStudentByStunum(String stunum) {
		Connection conn = null;
        PreparedStatement ps = null;
        ResultSet rs = null;
        Student s = null;
        List<Student> students = new ArrayList<Student>();
        System.out.println("学号" + "\t" + "姓名"+ "\t" + "班级"+ "\t" + "性别"+ "\t" + "Java成绩"+ "\t" + "数学成绩"+ "\t" + "英语成绩");  
        System.out.println("-----------------");  
        String sql ="select * from students where stunum like '%"+stunum+"%'";//班级
        try{
            conn = JDBCUtil.getConnection();
            ps = conn.prepareStatement(sql);
            rs = ps.executeQuery();
            while(rs.next()){
                s = new Student();
                s.setStunum(rs.getString("stunum"));
                s.setName(rs.getString("name"));
                s.setClasses(rs.getInt("classes"));
                s.setGender(rs.getString("gender"));
                s.getScoreList().get(0).setScore(rs.getDouble("javaScore"));
                s.getScoreList().get(1).setScore(rs.getDouble("mathScore"));
                s.getScoreList().get(2).setScore(rs.getDouble("englishScore"));
                students.add(s);
            }
            System.out.println(students);
        }catch(SQLException e){
            e.printStackTrace();
            try {
				throw new SQLException("查询所有数据失败");
			} catch (SQLException e1) {
				// TODO Auto-generated catch block
				e1.printStackTrace();
			}
        }finally{
            JDBCUtil.close(rs, ps, conn);
        }
		return students;
	}
	/**
     * 按 班级 查询学生成绩
     */
	@Override
	public List<Student> findStudentByClass(int classes) {
		Connection conn = null;
        PreparedStatement ps = null;
        ResultSet rs = null;
        Student s = null;
        List<Student> students = new ArrayList<Student>();
        String sql ="select * from students where classes like '%"+classes+"%'";//班级
        try{
            conn = JDBCUtil.getConnection();
            ps = conn.prepareStatement(sql);
            rs = ps.executeQuery();
            while(rs.next()){
                s = new Student();
                s.setStunum(rs.getString("stunum"));
                s.setName(rs.getString("name"));
                s.setClasses(rs.getInt("classes"));
                s.setGender(rs.getString("gender"));
                s.getScoreList().get(0).setScore(rs.getDouble("javaScore"));
                s.getScoreList().get(1).setScore(rs.getDouble("mathScore"));
                s.getScoreList().get(2).setScore(rs.getDouble("englishScore"));
                students.add(s);
            }
            System.out.println(students);
        }catch(SQLException e){
            e.printStackTrace();
            try {
				throw new SQLException("查询所有数据失败");
			} catch (SQLException e1) {
				// TODO Auto-generated catch block
				e1.printStackTrace();
			}
        }finally{
            JDBCUtil.close(rs, ps, conn);
        }
        return (List<Student>) students;
	}
	@Override
	public List<Student> diplayAllStudent() {
		// TODO Auto-generated method stub
		return null;
	}

	/**
	 * POI技术导出学生信息至Excel
	 */
	public  void LearningReport() throws SQLException {
			ArrayList<String>list1=new ArrayList<String>();
		
			Connection conn = null;
	        PreparedStatement ps = null;
	        ResultSet rs = null;
			String sql ="select * from students ";
	        conn = JDBCUtil.getConnection();
	        ps = conn.prepareStatement(sql);
	        rs = ps.executeQuery();
	        while(rs.next()){
	        	list1.add(0,rs.getString("stunum"));
	           	list1.add(1,rs.getString("name"));
	           	list1.add(2,rs.getString("classes"));
	           	list1.add(3,rs.getString("gender"));
	           	list1.add(4,rs.getString("javaScore"));
	           	list1.add(5,rs.getString("mathScore"));
	           	list1.add(6,rs.getString("englishScore"));
	           	System.out.println();
	        }
			HSSFWorkbook wb=new HSSFWorkbook();//创建Workbook对象（excel的文档对象）
			HSSFSheet sheet=wb.createSheet("表");
			HSSFRow row1=sheet.createRow(0);
			
			//设置单元格内容
			HSSFCell cell=row1.createCell(0);
			cell.setCellValue("学生成绩表");

			
			//合并单元格CellRangeAddress构造参数依次表示起始行，截至行，起始列， 截至列
	        sheet.addMergedRegion(new CellRangeAddress(0,0,0,6));
	        
	      //在sheet里创建第二行,创建单元格并设置单元格内容
	        HSSFRow row2=sheet.createRow(1);
	        row2.createCell(0).setCellValue("学号");
	        row2.createCell(1).setCellValue("名字");
	        row2.createCell(2).setCellValue("班级");
	        row2.createCell(3).setCellValue("性别");
	        row2.createCell(4).setCellValue("JAVA成绩");
	        row2.createCell(5).setCellValue("数学成绩");
	        row2.createCell(6).setCellValue("英语成绩");
	        
	      //在sheet里从第三行开始创建表格中学生信息
	        int j=1;
	        for(int i=0;i<list1.size();i++) {
	        	HSSFRow row=sheet.createRow(++j);
	        	 row.createCell(0).setCellValue(list1.get(i++));
	             row.createCell(1).setCellValue(list1.get(i++));
	             row.createCell(2).setCellValue(list1.get(i++));
	             row.createCell(3).setCellValue(list1.get(i++));
	             row.createCell(4).setCellValue(list1.get(i++));
	             row.createCell(5).setCellValue(list1.get(i++));
	             row.createCell(6).setCellValue(list1.get(i));
	        }
	        
	        //导入Excel表
	        try {
	        	FileOutputStream fout=new FileOutputStream("C:\\Users\\小糖人222\\Desktop\\students.xlsx");
	        	wb.write(fout);
	        	fout.close();
	        }catch(Exception e){
	        	e.printStackTrace();
	        }
	}

	@Override
	public Student readStudent(String name) {
		// TODO Auto-generated method stub
		return null;
	}

	

	


}